/* Payment Platform from Lucky Byte, Inc.
 *
 * Copyright (c) 2016 Lucky Byte, Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.lucky_byte.pay.tpdu;

import java.io.File;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

/**
 * 数据库操作
 */
public class Derby
{
	private static final Logger logger = LogManager.getLogger();
	private static Derby instance = null;

	public static Derby getInstance() {
		if (instance == null) {
			logger.error("数据库尚未建立连接，这是程序错误，请联系开发人员.");
			return null;
		}
		return instance;
	}
	private String jdbc_url;
	private Connection connection;

	public Derby(String db_base_path) {
		String db_path = db_base_path + "/db";
		jdbc_url = "jdbc:derby:" + db_path;
		File file = new File(db_path);
		if (!file.exists()) {
			jdbc_url = jdbc_url + ";create=true";
		}
		instance = this;
	}

	/**
	 * 建立数据库连接
	 * 必须在使用数据库操作前建立数据库连接
	 */
	public boolean open() {
		try {
			connection = DriverManager.getConnection(jdbc_url);
			if (jdbc_url.endsWith("create=true")) {
				connection.createStatement().execute(
						"create table tpdu_routes("
								+ "i_r_host varchar(128),"
								+ "i_r_port int,"
								+ "i_l_host varchar(128),"
								+ "i_l_port int,"
								+ "o_r_host varchar(128),"
								+ "o_r_port int,"
								+ "o_l_host varchar(128),"
								+ "o_l_port int,"
								+ "i_size   int,"
								+ "o_size   int"
								+ ")");
				connection.commit();
			}
			return true;
		} catch (SQLException e) {
			logger.error("连接数据库[{}]错误[{}].", jdbc_url, e.getMessage());
			return false;
		}
	}

	/**
	 * 关闭数据库连接
	 */
	public void close() {
		try {
			DriverManager.getConnection("jdbc:derby:;shutdown=true");
		} catch (SQLException e) {
			logger.catching(e);
		}
	}

	/**
	 * 备份数据库(热备份)
	 */
	public void backup(String tpdu_base) {
		if (connection == null) {
			logger.error("不能备份数据库，未建立数据库连接.");
			return;
		}
		String today = new SimpleDateFormat("yyyyMMdd")
				.format(new Date());
		String back_path = tpdu_base + "/db_backup/" + today;
		File back_dir = new File(back_path);
		if (back_dir.exists()) {
			logger.warn("目录[{}]已经存在，放弃备份数据库.", back_dir.getPath());
			return;
		}
		if (!back_dir.mkdirs()) {
			logger.error("创建目录[{}]错误，备份数据库失败.",
					back_dir.getPath());
			return;
		}
		try {
			CallableStatement statement = connection.prepareCall(
					"CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)");
			statement.setString(1, back_path);
			statement.execute(); 
			statement.close();
		} catch (SQLException e) {
			logger.error("备份数据库到[{}]错误[{}].", back_path, e.getMessage());
		}
	}

	private void setStatementString(PreparedStatement statement, int i,
			Map<String, Object> record, String key) throws SQLException {
		if (!record.containsKey(key)) {
			statement.setString(i, "");
		}
		Object value = record.get(key);
		if (value != null && value instanceof String) {
			statement.setString(i, (String) value);
			return;
		}
		logger.warn("列[{}]为字符串类型，参数类型为[{}].", key,
				value != null ? value.getClass().getSimpleName() : "NULL");
		statement.setString(i, "");
	}

	private void setStatementInt(PreparedStatement statement, int i,
			Map<String, Object> record, String key) throws SQLException {
		if (!record.containsKey(key)) {
			statement.setInt(i, 0);
		}
		Object value = record.get(key);
		if (value != null && value instanceof Integer) {
			statement.setInt(i, (int) value);
			return;
		}
		logger.warn("列[{}]为整型，参数类型为[{}].", key,
				value != null ? value.getClass().getSimpleName() : "NULL");
		statement.setInt(i, 0);
	}

	/**
	 * 插入 tpdu_routes 数据记录
	 */
	public void insertRoutes(Map<String, Object> record) {
		if (connection == null) {
			logger.warn("数据库未连接，不能插入 route 记录.");
			return;
		}
		try {
			PreparedStatement statement = connection.prepareStatement(
					"insert into tpdu_routes values("
					+ "?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
			this.setStatementString(statement, 1, record, "i_r_host");
			this.setStatementInt   (statement, 2, record, "i_r_port");
			this.setStatementString(statement, 3, record, "i_l_host");
			this.setStatementInt   (statement, 4, record, "i_l_port");
			this.setStatementString(statement, 5, record, "o_r_host");
			this.setStatementInt   (statement, 6, record, "o_r_port");
			this.setStatementString(statement, 7, record, "o_l_host");
			this.setStatementInt   (statement, 8, record, "o_l_port");
			this.setStatementInt   (statement, 9, record, "i_size");
			this.setStatementInt   (statement, 10, record, "o_size");
			statement.executeUpdate();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			logger.error("插入 Route 记录错误[{}].", e.getMessage());
		} catch (Exception e) {
			logger.catching(e);
		}
	}
}
